package br.unirio.webdisco.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import br.unirio.webdisco.dao.conn.BasePostgresDAO;
import br.unirio.webdisco.entity.Doenca;
import br.unirio.webdisco.entity.Usuario;

public class DoencaPostgresDAO extends BasePostgresDAO<Doenca> {

	@Override
	public void incluir(Doenca doenca) throws Exception {
		Connection connection = criaConexao(false);
		String sql = "INSERT INTO Doenca (IdDoenca, Doenca) ";
			   sql += "VALUES (nextval('SEQ_IdDoenca'),?);";
			   
			   
		PreparedStatement stmt = null;

		try {
			stmt = connection.prepareStatement(sql);

			stmt.setString(1, doenca.getDoenca());	
			
			int retorno = stmt.executeUpdate();
			if (retorno == 0) {
				connection.rollback();
				throw new SQLException(
						"Ocorreu um erro inesperado no momento de inserir dados de local no banco!");
			}
			connection.commit();

		} catch (SQLException e) {
			connection.rollback();
			throw e;
		} finally {
			try {
				stmt.close();
				this.fechaConexao();
			} catch (SQLException e) {
				throw e;
			}

		}	
		
	}

	@Override
	public void alterar(Doenca usuario) throws Exception {
		Connection connection = criaConexao(false);
		String sql =  "UPDATE usuario SET IdPerfil = 1, Nome = ?, SobreNome = ?, Email = ?, Senha = ? ";
			   sql += "WHERE idDoenca = ?";
			   
		PreparedStatement stmt = null;
		try {
			stmt = connection.prepareStatement(sql);

			stmt.setInt(5, usuario.getIdDoenca());
			
			int retorno = stmt.executeUpdate();
			if (retorno == 0) {
				connection.rollback();
				throw new SQLException(
						"Ocorreu um erro inesperado no momento de alterar dados de local no banco!");
			}
			connection.commit();

		} catch (SQLException e) {
			connection.rollback();
			throw e;
		} finally {
			try {
				stmt.close();
				this.fechaConexao();
			} catch (SQLException e) {
				throw e;
			}

		}		
	}

	@Override
	public void excluir(Doenca usuario) throws Exception {
		Connection connection = criaConexao(false);
		String sql = "DELETE FROM usuario WHERE IdDoenca = ?";
		PreparedStatement stmt = null;
		try {
			stmt = connection.prepareStatement(sql);
			stmt.setInt(1, usuario.getIdDoenca());
			
			int retorno = stmt.executeUpdate();
			if (retorno == 0) {
				connection.rollback();
				throw new SQLException(
						"Ocorreu um erro inesperado no momento de remover dados de local no banco!");
			}
			connection.commit();

		} catch (SQLException e) {
			connection.rollback();
			throw e;
		} finally {
			try {
				stmt.close();
				this.fechaConexao();
			} catch (SQLException e) {
				throw e;
			}

		}
	}

	@Override
	public Integer count() throws Exception {
		Connection connection = criaConexao(false);
		String sql = "SELECT COUNT(*) AS Count FROM USUARIO ";
		
		
		PreparedStatement stmt = null;
		ResultSet rs = null;
		Integer countDoenca = null;
		
		try {
			stmt = connection.prepareStatement(sql);
			rs = stmt.executeQuery();
			while (rs.next()) 	
				countDoenca = rs.getInt("Count");
				
			
		} catch (SQLException e) {
			throw e;
		} finally {
			try {
				stmt.close();
				rs.close();
				this.fechaConexao();
			} catch (SQLException e) {
				throw e;
			}

		}
		return countDoenca;
	}
	
	@Override
	public List<Doenca> listar(Integer paginaTamanho, Integer paginaAtual) throws Exception {
		Connection connection = criaConexao(false);
		String sql = "SELECT * FROM Doenca ";
		
		PreparedStatement stmt = null;
		ResultSet rs = null;
		Doenca doenca= null;
		List<Doenca> listaDoenca = new ArrayList<Doenca>();
		
		try {
			stmt = connection.prepareStatement(sql);
			rs = stmt.executeQuery();
			while (rs.next()) 
			{
				doenca = new Doenca();
				doenca.setIdDoenca(rs.getInt("IdDoenca"));
				doenca.setDoenca(rs.getString("Doenca"));
				
				listaDoenca.add(doenca);
			}
		} catch (SQLException e) {
			throw e;
		} finally {
			try {
				stmt.close();
				rs.close();
				this.fechaConexao();
			} catch (SQLException e) {
				throw e;
			}

		}
		return listaDoenca;
	}
	
	public Doenca consultarDoenca(String u, String p) throws Exception {
		
		Connection connection = criaConexao(false);
		String sql = "SELECT * FROM Doenca WHERE login = ? AND senha = ?";
		PreparedStatement statement =  null;
		ResultSet rs = null;
		Doenca usuario = null;
		
		try {
			statement = connection.prepareStatement(sql);
			statement.setString(1, u);
			statement.setString(2, p);
			
			rs = statement.executeQuery();
			
			if (rs.next()) {
				usuario = new Doenca();

			}
			else {
				return null;
			}
		}
		catch (SQLException e) {
			System.out.println(e.getMessage());
		}
		finally {
			try {
				if(statement != null)
					statement.close();
				if(rs != null)
					rs.close();
				this.fechaConexao();
			}
			catch (SQLException e) {
				System.out.println(e.getMessage());
			}
		}
		return usuario;
	}

	@Override
	public Doenca consultarPorId(Integer id) throws Exception {
		Connection connection = criaConexao(false);
		String sql = "SELECT * FROM Doenca WHERE IdDoenca = ?";
		PreparedStatement stmt = null;
		ResultSet rs = null;
		Doenca usuario= null;
		try {
			stmt = connection.prepareStatement(sql);
			stmt.setInt(1, id);
			rs = stmt.executeQuery();
			if (rs.next()) {
				usuario = new Doenca();
				usuario.setIdDoenca(rs.getInt("IdDoenca"));
	
			}
		} catch (SQLException e) {
			throw e;
		} finally {
			try {
			
				if(stmt != null)
					stmt.close();
				
				if(rs != null)
					rs.close();
				
				this.fechaConexao();

			} catch (SQLException e) {
				throw e;
			}

		}
		return usuario;
	}

	public Doenca consultarPorNome(String nome) throws Exception {
		Connection connection = criaConexao(false);
		String sql = "SELECT * FROM Doenca WHERE Doenca = ?";
		PreparedStatement stmt = null;
		ResultSet rs = null;
		Doenca doenca= null;
		try {
			stmt = connection.prepareStatement(sql);
			stmt.setString(1, nome);
			
			rs = stmt.executeQuery();
			if (rs.next()) {
				doenca = new Doenca();
				doenca.setIdDoenca(rs.getInt("IdDoenca"));
				doenca.setDoenca(rs.getString("Doenca"));
				
			}
		} catch (SQLException e) {
			throw e;
		} finally {
			try {
			
				if(stmt != null)
					stmt.close();
				
				if(rs != null)
					rs.close();
				
				this.fechaConexao();

			} catch (SQLException e) {
				throw e;
			}

		}
		return doenca;
	}
	
	@Override
	public ArrayList<Doenca> consultar(String parametros) throws Exception {
		Connection connection = criaConexao(false);
		String sql = "SELECT * FROM Doenca WHERE 1 = 1 ";
		sql += parametros;
		
		PreparedStatement stmt = null;
		ResultSet rs = null;
		Doenca usuario= null;
		ArrayList<Doenca> listaDoenca = new ArrayList<Doenca>();
		
		try {
			stmt = connection.prepareStatement(sql);
			rs = stmt.executeQuery();
			while (rs.next()) 
			{
				usuario = new Doenca();
				usuario.setIdDoenca(rs.getInt("IdDoenca"));

				
				listaDoenca.add(usuario);
				
			}
		} catch (SQLException e) {
			throw e;
		} finally {
			try {
				stmt.close();
				rs.close();
				this.fechaConexao();
			} catch (SQLException e) {
				throw e;
			}

		}
		return listaDoenca;
	}
	}

